
clear all

/// Creates county-year level dataset with covariates from the ACS, hosted at nhis.org

/*
county-level data for were downloaded at NHGIS: 

 Steven Manson, Jonathan Schroeder, David Van Riper, Katherine Knowles, Tracy Kugler, Finn Roberts, and Steven Ruggles. IPUMS National Historical Geographic Information System: Version 18.0 [dataset]. Minneapolis, MN: IPUMS. 2023. http://doi.org/10.18128/D050.V18.0


// download the following tables at the county-level: 
--------------------------------------------------------------------------------
Data Summary
--------------------------------------------------------------------------------
 
Years (all ACS 5-year):             2005-2009, 2010-2014, 2015-2019
Geographic level: County (by State)

 
Tables:
 
1. Sex by Age
   Universe:    Total population
   Source code: B01001
   NHGIS code:  ALT0
 
2. Total Population
   Universe:    Total population
   Source code: B01003
   NHGIS code:  ALUB
 
3. Hispanic or Latino Origin by Race
   Universe:    Total population
   Source code: B03002
   NHGIS code:  ALUK
 
4. Educational Attainment for the Population 25 Years and Over
   Universe:    Population 25 years and over
   Source code: B15003
   NHGIS code:  ALWG
 
5. Ratio of Income to Poverty Level in the Past 12 Months
   Universe:    Population for whom poverty status is determined
   Source code: C17002
   NHGIS code:  ALWV
 
6. Median Household Income in the Past 12 Months (in 2019 Inflation-Adjusted Dollars)
   Universe:    Households
   Source code: B19013
   NHGIS code:  ALW1
 
7. Employment Status for the Population 16 Years and Over
   Universe:    Population 16 years and over
   Source code: B23025
   NHGIS code:  ALY3
	
*/


// 2007 

cd "$InputPath/nhgis0003_csv"
import delimited "nhgis0003_ds195_20095_2009_county.csv", clear

drop year
gen year=2007

rename (countya statea county state) (fcounty fstate county_nghis state_nhgis)

/// SEX BY AGE ///

rename (rkye002 rkye026) (male female)

egen age0_14=rowtotal(rkye003-rkye005 rkye027-rkye029), missing
egen male0_24=rowtotal(rkye003-rkye010), missing
egen female0_24=rowtotal(rkye027-rkye034), missing
egen age0_24=rowtotal(male0_24 female0_24), missing

egen age0_5=rowtotal(rkye003 rkye027), missing 
egen age5_17=rowtotal(rkye004-rkye006 rkye028-rkye030)

egen male15_24=rowtotal(rkye006-rkye010), missing
egen female15_24=rowtotal(rkye030-rkye034), missing
egen age15_24=rowtotal(male15_24 female15_24), missing

egen male65_up=rowtotal(rkye020-rkye025), missing
egen female65_up=rowtotal(rkye044-rkye049), missing
egen age65_up=rowtotal(male65_up female65_up), missing

egen vap=rowtotal(rkye007-rkye025 rkye031-rkye049), missing

/// TOTAL POPULATION ///

rename rk9e001 population 

/// RACE ///

rename (rlae002-rlae006) (white black aian asian nhopi)
egen other= rowtotal(rlae007-rlae010), missing //other race alone and multi-racial

/// HISPANIC OR LATINO BY RACE ///

rename (rlie003 rlie004 rlie012)(whitenh blacknh hispanic)

/// EDUCATIONAL ATTAINMENT /// 	(of population 25 or older)

egen nohsdiploma=rowtotal(rm8e003-rm8e010 rm8e020-rm8e027), missing
egen bachelors=rowtotal(rm8e015-rm8e018 rm8e032-rm8e035), missing
egen malenohsdiploma=rowtotal(rm8e003-rm8e010), missing
egen femalenohsdiploma=rowtotal(rm8e020-rm8e027), missing

/// RATIO OF INCOME TO PVERTY LEVEL IN PAST 12 MONTHS ///

egen belowpoverty=rowtotal(rnbe002 rnbe003), missing

/// MEDIAN HOUSEHOLD INCOME ///	(2014 inflation adjusted dollars)

rename (rnhe001 rnie001 rnje001 rnke001 rnle001 rnme001) (medhhinc medhhinc_w medhhinc_b medhhinc_aian medhhinc_asian medhhinc_nhopi), missing
egen medhhinc_other=rowmean(rnne001 rnoe001)
rename (rnpe001 rnqe001) (medhhinc_wnh medhhinc_hispanic)

foreach i of varlist medhhinc* {
replace `i'=`i'*1.07 // ACS estimates reported in 2009 USD, multiply by 1.07 (BLS) to put in 2012 dollars
}

/// EMPLOYMENT ///
egen maleunemployed=rowtotal(j6qe008 j6qe015 j6qe022 j6qe029 j6qe036 j6qe043 j6qe050 j6qe057 j6qe064 j6qe071 j6qe076 j6qe081 j6qe086),missing
egen femaleunemployed=rowtotal(j6qe094 j6qe101 j6qe108 j6qe115 j6qe122 j6qe129 j6qe136 j6qe143 j6qe150 j6qe157 j6qe162 j6qe167 j6qe172), missing
egen unemployed=rowtotal(maleunemployed femaleunemployed), missing

keep year state_nhgis fstate county_nghis fcounty vap male* female* age* population ///
	 white black aian asian nhopi other whitenh blacknh hispanic ///
	 nohsdiploma bachelors malenohsdiploma femalenohsdiploma ///
	 belowpoverty medhhinc* unemployed

tempfile y2007 
save `y2007', replace


/// 2012

cd "$InputPath/nhgis0007_csv"
import delimited "nhgis0007_ds206_20145_2014_county.csv", clear


rename (countya statea county state) (fcounty fstate county_nghis state_nhgis)

/// SEX BY AGE ///

rename (abaqe002 abaqe026) (male female)

egen age0_14=rowtotal(abaqe003-abaqe005 abaqe027-abaqe029), missing
egen male0_24=rowtotal(abaqe003-abaqe010), missing
egen female0_24=rowtotal(abaqe027-abaqe034), missing
egen age0_24=rowtotal(male0_24 female0_24), missing

egen age0_5=rowtotal(abaqe003 abaqe027), missing 
egen age5_17=rowtotal(abaqe004-abaqe006 abaqe028-abaqe030)

egen male15_24=rowtotal(abaqe006-abaqe010), missing
egen female15_24=rowtotal(abaqe030-abaqe034), missing
egen age15_24=rowtotal(male15_24 female15_24), missing

egen male65_up=rowtotal(abaqe020-abaqe025), missing
egen female65_up=rowtotal(abaqe044-abaqe049), missing
egen age65_up=rowtotal(male65_up female65_up), missing

egen vap=rowtotal(abaqe007-abaqe025 abaqe031-abaqe049), missing

/// TOTAL POPULATION ///

rename aba1e001 population 

/// RACE ///

rename (aba2e002-aba2e006) (white black aian asian nhopi)
egen other= rowtotal(aba2e007-aba2e010), missing //other race alone and multi-racial

/// HISPANIC OR LATINO BY RACE ///

rename (abbae003 abbae004 abbae012)(whitenh blacknh hispanic)

/// EDUCATIONAL ATTAINMENT /// 	(of population 25 or older)

egen nohsdiploma=rowtotal(abc4e002-abc4e016), missing
egen bachelors=rowtotal(abc4e022-abc4e025), missing

/// RATIO OF INCOME TO PVERTY LEVEL IN PAST 12 MONTHS ///

egen belowpoverty=rowtotal(abdje002 abdje003), missing

/// MEDIAN HOUSEHOLD INCOME ///	(2014 inflation adjusted dollars)

rename (abdpe001 abdqe001 abdre001 abdse001 abdte001 abdue001) (medhhinc medhhinc_w medhhinc_b medhhinc_aian medhhinc_asian medhhinc_nhopi), missing
egen medhhinc_other=rowmean(abdve001  abdwe001)
rename (abdxe001 abdye001) (medhhinc_wnh medhhinc_hispanic)

foreach i of varlist medhhinc*{
replace `i'=`i'*.97
}

/// EMPLOYMENT STATUS ///

rename (abgfe002 abgfe004 abgfe005 abgfe006) (laborforce employed unemployed armedforces) 

/// SAVE DATA ///

keep state_nhgis fstate county_nghis fcounty vap male* female* age* population ///
	 white black aian asian nhopi other whitenh blacknh hispanic ///
	 nohsdiploma bachelors belowpoverty medhhinc* unemployed

gen year=2012


tempfile y2012
save `y2012', replace


/// 2017

cd "$InputPath/nhgis0023_csv 2"
import delim "nhgis0023_ds244_20195_county.csv", clear


rename (countya statea county ) (fcounty fstate county_nghis)
 

/// AGE BY SEX ///

rename (alt0e002 alt0e026) (male female)

egen age0_14=rowtotal(alt0e003-alt0e005 alt0e027-alt0e029), missing
egen male0_24=rowtotal(alt0e003-alt0e010), missing
egen female0_24=rowtotal(alt0e027-alt0e034), missing
egen age0_24=rowtotal(male0_24 female0_24), missing

egen age0_5=rowtotal(alt0e003 alt0e027), missing 
egen age5_17=rowtotal(alt0e004-alt0e006 alt0e028-alt0e030)

egen male15_24=rowtotal(alt0e006-alt0e010), missing
egen female15_24=rowtotal(alt0e030-alt0e034), missing
egen age15_24=rowtotal(male15_24 female15_24), missing

egen male65_up=rowtotal(alt0e020-alt0e025), missing
egen female65_up=rowtotal(alt0e044-alt0e049), missing
egen age65_up=rowtotal(male65_up female65_up), missing

egen vap=rowtotal(alt0e007-alt0e025 alt0e031-alt0e049), missing

/// TOTAL POPULATION ///
rename alube001 population

/// RACE ///

egen white=rowtotal(aluke003 aluke013), missing
egen black=rowtotal(aluke004 aluke014), missing
egen aian=rowtotal(aluke005 aluke015), missing
egen asian=rowtotal(aluke006 aluke016), missing
egen nhopi=rowtotal(aluke007 aluke017), missing
egen other=rowtotal(aluke008-aluke011 aluke018-aluke021), missing

/// HISPANIC OR LATINO BY RACE ///

rename (aluke003 aluke004 aluke012)(whitenh blacknh hispanic)


/// EDUCATIONAL ATTAINMENT /// 	(of population 25 or older)

egen nohsdiploma=rowtotal(alwge002-alwge016), missing
egen bachelors=rowtotal(alwge022-alwge025), missing

/// RATIO OF INCOME TO PVERTY LEVEL IN PAST 12 MONTHS ///

egen belowpoverty=rowtotal(alwve002 alwve003), missing

/// MEDIAN HOUSEHOLD INCOME ///	(2018 inflation adjusted dollars)

rename (alw1e001) (medhhinc)
replace medhhinc=medhhinc*.98 // converted to 2017

/// EMPLOYMENT ///
rename (  aly3e005 ) (  unemployed ) 

keep county_nghis fstate fcounty male* female* age* vap population white black aian asian nhopi other whitenh blacknh hispanic nohsdiploma bachelors belowpoverty medhhinc unemployed

gen year=2017

append using `y2012'
append using `y2007'

drop m1 

cd "$OutputPath/"
save nhgis_county, replace

